package com.b2c.repository;

import java.util.ArrayList;
import java.util.List;

import javax.annotation.Resource;

import com.b2c.entity.vo.GoodsSalesAnalyseVo;
import com.b2c.entity.vo.GoodsSpecSalesAnalyseVo;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;

import com.b2c.entity.result.PagingResponse;

@Repository
public class GoodsSalesAnalyseRepository {
    @Resource
    private JdbcTemplate jdbcTemplate;

    protected int getTotalSize() {
        return jdbcTemplate.queryForObject("SELECT FOUND_ROWS() as row_num;", int.class);
    }

    @Transactional
    public PagingResponse<GoodsSalesAnalyseVo> getGoodsSalesReport(Integer pageIndex, Integer pageSize, Integer shopId, String goodsNum) {
		StringBuilder sql = new StringBuilder();
        sql.append("SELECT eg.`name`,eg.`number`,eg.image ,eg.id AS goodsId ");
        sql.append(",IFNULL((SELECT SUM(quantity) FROM erp_order_send_item WHERE goodsId=eg.id),0) AS total");
        sql.append(",IFNULL((SELECT SUM(quantity) FROM erp_order_send_item AS oi LEFT JOIN erp_order_send AS o ON o.id = oi.orderSendId WHERE oi.goodsId=eg.id AND datediff(NOW(),o.orderTime)=1),0) AS DAY1");
        sql.append(",IFNULL((SELECT SUM(quantity) FROM erp_order_send_item AS oi LEFT JOIN erp_order_send AS o ON o.id = oi.orderSendId WHERE oi.goodsId=eg.id AND datediff(NOW(),o.orderTime)<=3),0) AS DAY3");
        sql.append(",IFNULL((SELECT SUM(quantity) FROM erp_order_send_item AS oi LEFT JOIN erp_order_send AS o ON o.id = oi.orderSendId WHERE oi.goodsId=eg.id AND datediff(NOW(),o.orderTime)<=7),0) AS DAY7");
        sql.append(",IFNULL((SELECT SUM(quantity) FROM erp_order_send_item AS oi LEFT JOIN erp_order_send AS o ON o.id = oi.orderSendId WHERE oi.goodsId=eg.id AND datediff(NOW(),o.orderTime)<=15),0) AS DAY15");
        sql.append(",IFNULL((SELECT SUM(quantity) FROM erp_order_send_item AS oi LEFT JOIN erp_order_send AS o ON o.id = oi.orderSendId WHERE oi.goodsId=eg.id AND datediff(NOW(),o.orderTime)<=30),0) AS DAY30");
        sql.append(",IFNULL((SELECT SUM(quantity) FROM erp_order_send_return osr WHERE osr.goodsId=eg.id),0) AS returnTotal ");
        sql.append(" from erp_goods eg ");
        sql.append(" WHERE eg.isDelete=0 AND eg.`status`=1 ");
        

        List<Object> params = new ArrayList<>();
        if(shopId != null && shopId >0){
            
        }
        if(StringUtils.hasText(goodsNum)){
            sql.append(" AND eg.`number` = ? ");
            params.add(goodsNum);
        }

        sql.append(" ORDER BY DAY30 DESC ");

        sql.append("  LIMIT ?,?");
        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);
        var list = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(GoodsSalesAnalyseVo.class),
                params.toArray(new Object[params.size()]));

        int totalSize = getTotalSize();
		return new PagingResponse<>(pageIndex, pageSize, totalSize, list);
	}

    public List<GoodsSpecSalesAnalyseVo> getGoodsSpecSalesReport(Integer goodsId) {
		StringBuilder sql = new StringBuilder("SELECT ");
        sql.append(" egs.id AS specId,egs.goodsId,SUM(osi.quantity) AS sales,egs.color_value,egs.size_value ");
        sql.append(" FROM  erp_order_send_item osi ");
        sql.append(" LEFT JOIN erp_goods_spec egs ON egs.id=osi.goodsSpecId ");
        sql.append(" WHERE osi.goodsId = "+goodsId);
        sql.append(" GROUP BY osi.goodsSpecId ");
        sql.append(" ORDER BY sales DESC ");
		return  jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(GoodsSpecSalesAnalyseVo.class));
	}

    public List<GoodsSalesAnalyseVo> getGoodsSalesReportPdd(Integer shopId) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT A.*,sg.goodsName as `name`,sg.thumbUrl as image FROM (");
        sql.append("SELECT oi.pdd_good_id AS goodsId,");//,oi.goodsName AS `name`,oi.goodsImg AS image,oi.goodsNum AS `number`
        sql.append("SUM(quantity) AS total,SUM(itemAmount) AS amount");
        sql.append(",IFNULL((SELECT SUM(quantity) FROM dc_pdd_orders_item AS oii LEFT JOIN dc_pdd_orders o ON o.id = oii.order_id WHERE oii.pdd_good_id=oi.pdd_good_id AND datediff(NOW(),o.created_time)=0),0) AS DAY0");
        sql.append(",IFNULL((SELECT SUM(quantity) FROM dc_pdd_orders_item AS oii LEFT JOIN dc_pdd_orders o ON o.id = oii.order_id WHERE oii.pdd_good_id=oi.pdd_good_id AND datediff(NOW(),o.created_time)=1),0) AS DAY1");
        sql.append(",IFNULL((SELECT SUM(quantity) FROM dc_pdd_orders_item AS oii LEFT JOIN dc_pdd_orders o ON o.id = oii.order_id WHERE oii.pdd_good_id=oi.pdd_good_id AND datediff(NOW(),o.created_time)=2),0) AS DAY2");
        sql.append(",IFNULL((SELECT SUM(quantity) FROM dc_pdd_orders_item AS oii LEFT JOIN dc_pdd_orders o ON o.id = oii.order_id WHERE oii.pdd_good_id=oi.pdd_good_id AND datediff(NOW(),o.created_time)<=3),0) AS DAY3");
        sql.append(",IFNULL((SELECT SUM(quantity) FROM dc_pdd_orders_item AS oii LEFT JOIN dc_pdd_orders o ON o.id = oii.order_id WHERE oii.pdd_good_id=oi.pdd_good_id AND datediff(NOW(),o.created_time)<=7),0) AS DAY7");
        sql.append(",IFNULL((SELECT SUM(quantity) FROM dc_pdd_orders_item AS oii LEFT JOIN dc_pdd_orders o ON o.id = oii.order_id WHERE oii.pdd_good_id=oi.pdd_good_id AND datediff(NOW(),o.created_time)<=15),0) AS DAY15");
        sql.append(",IFNULL((SELECT SUM(quantity) FROM dc_pdd_orders_item AS oii LEFT JOIN dc_pdd_orders o ON o.id = oii.order_id WHERE oii.pdd_good_id=oi.pdd_good_id AND datediff(NOW(),o.created_time)<=30),0) AS DAY30");

        sql.append(" FROM dc_pdd_orders_item oi");
        sql.append(" LEFT JOIN dc_pdd_orders oo ON oo.id=oi.order_id ");
        sql.append(" WHERE oi.pdd_good_id not in (419986003167,415222366821,390312840454,389898404984,390300479590,393425232129,394802014653) ");
        
        List<Object> params = new ArrayList<>();
        if(shopId != null && shopId >0){

            sql.append(" and oo.shopId=?  ");
            params.add(shopId);
        }
        sql.append(" GROUP BY oi.pdd_good_id");
        sql.append(" ORDER BY DAY1 DESC,DAY3 DESC,DAY30 DESC ");
        sql.append(" ) AS A ");
        sql.append(" LEFT JOIN dc_shop_goods as sg on sg.goodsId = A.goodsId");
        System.out.println(sql.toString());
		return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(GoodsSalesAnalyseVo.class),params.toArray(new Object[params.size()]));
	}
}
